﻿DROP EVENT IF EXISTS ticket_statistic_event;
delimiter //
CREATE EVENT ticket_statistic_event
  ON SCHEDULE EVERY  1 DAY 
    STARTS  date_add(date_add(curdate(), interval 1 day), interval 2 hour)
    DO
      BEGIN
        set @d_begin = date_add(curdate(), interval -1 day);
        set @d_end = curdate();

        -- query dialy closed ticket, sum count by by province, city, project and address then insert into itsm_daily_addr_sum
        INSERT INTO itsm_daily_addr_sum (province,city,project, address, sumtime, ticketnum)
            SELECT a.province, a.city, CONCAT(a.projectid,'-',a.project), a.address, a.sumtime, count(*) FROM (
                SELECT  REPLACE(JSON_EXTRACT(extprops,'$.provincename'),'\"','') province,  
                        REPLACE(JSON_EXTRACT(extprops,'$.cityname') ,'\"','') city, 
                        REPLACE(JSON_EXTRACT(extprops,'$.projectname'),'\"','') project,
                        REPLACE(JSON_EXTRACT(extprops,'$.address'),'\"','') address,
                        ITSM_TICKET.projectid,
                        @d_begin sumtime
                FROM ITSM_TICKET, ITSM_TICKET_EXTENSE
                WHERE ITSM_TICKET.ID = ITSM_TICKET_EXTENSE.ID
                AND UPDATETIME BETWEEN @d_begin AND @d_end AND STATUS = 4) a
            GROUP BY a.province, a.city, CONCAT(a.projectid,'-',a.project), a.address, a.sumtime;

        INSERT INTO itsm_daily_sum (province,city, sumtime, ticketnum)
            SELECT PROVINCE, CITY, @d_begin, COUNT(*)
            FROM ITSM_DAILY_ADDR_SUM
            WHERE SUMTIME = @d_begin
            GROUP BY PROVINCE, CITY;
        
        -- at first day of every month we statistic last month ticket from daily statistic table
        IF DAYOFMONTH(@d_end) = 1 THEN
            set @d_begin = date_add(@d_end, interval -1 month);
            INSERT INTO itsm_month_addr_sum (province, city, project, address, sumtime, ticketnum)
                SELECT province, city, project, address, @d_begin, sum(ticketnum)
                FROM itsm_daily_addr_sum
                WHERE sumtime >= @d_begin
                GROUP BY province, city, project, address;
            
            INSERT INTO itsm_month_sum (province,city, sumtime, ticketnum)
                SELECT PROVINCE, CITY, @d_begin, sum(ticketnum)
                FROM ITSM_DAILY_SUM
                WHERE SUMTIME >= @d_begin
                GROUP BY PROVINCE, CITY; 
                   
            -- clean last month data from daily table
            DELETE FROM itsm_daily_sum WHERE sumtime >= @d_begin;
            DELETE FROM itsm_daily_addr_sum WHERE sumtime >= @d_begin;
        END IF;
        
        -- at first day of every year, we statistic last year ticket from month statistic table
        IF MONTH(@d_end) = 1 AND DAYOFMONTH(@d_end) = 1 THEN
            set @d_begin = date_add(@d_end, interval -1 year);
            INSERT INTO itsm_year_addr_sum (province, city, project, address, sumtime, ticketnum)
                SELECT province, city, project, address, @d_begin, sum(ticketnum)
                FROM itsm_month_addr_sum
                WHERE sumtime >= @d_begin
                GROUP BY province, city, project, address;

            INSERT INTO itsm_year_sum (province, city, sumtime, ticketnum)
                SELECT province, city, @d_begin, sum(ticketnum)
                FROM itsm_month_sum
                WHERE sumtime >= @d_begin
                GROUP BY province, city;
                
            -- clean last year data from month table
            DELETE FROM itsm_month_sum WHERE sumtime >= @d_begin;
            DELETE FROM itsm_month_addr_sum WHERE sumtime >= @d_begin;
        END IF;
        
    END;\p
    //
delimiter ;
